package com.guldan.thrall.util;


import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.guldan.jaina.dto.area.AreaDTO;

/**
 * @author Hongten
 * @created 2014-5-20
 */
public class Excel {

    public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
    public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";

    public static final String EMPTY = "";
    public static final String POINT = ".";
    public static final String LIB_PATH = "lib";
    public static final String STUDENT_INFO_XLS_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2003_POSTFIX;
    public static final String STUDENT_INFO_XLSX_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2010_POSTFIX;
    public static final String NOT_EXCEL_FILE = " : Not the Excel file!";
    public static final String PROCESSING = "Processing...";

    /**
     * read the Excel file
     * @param path the path of the Excel file
     * @return
     * @throws IOException
     */
    public List<AreaDTO> readExcel(String path) throws IOException {
        if (path == null || EMPTY.equals(path)) {
            return null;
        } else {
            String postfix = getPostfix(path);
            if (!EMPTY.equals(postfix)) {
                if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    return readXls(path);
                } else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
                    return readXlsx(path);
                }
            } else {
                System.out.println(path + NOT_EXCEL_FILE);
            }
        }
        return null;
    }

    /**
     * Read the Excel 2010
     * @param path the path of the excel file
     * @return
     * @throws IOException
     */
    public List<AreaDTO> readXlsx(String path) throws IOException {
        System.out.println(PROCESSING + path);
        InputStream is = new FileInputStream(path);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        AreaDTO area = null;
        List<AreaDTO> list = new ArrayList<AreaDTO>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            
            System.out.println("xssfSheet.getPhysicalNumberOfRows():" + xssfSheet.getPhysicalNumberOfRows());
            // Read the Row
            for (int rowNum = 0; rowNum <= xssfSheet.getPhysicalNumberOfRows(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                	System.out.println("rowNum:" + rowNum);
                	area = new AreaDTO();
                	
                    XSSFCell areaCode = xssfRow.getCell(1);
                    XSSFCell areaName = xssfRow.getCell(2);
                    XSSFCell areaNameZw = xssfRow.getCell(3);
                    XSSFCell areaLevleCode = xssfRow.getCell(4);
                    XSSFCell areaParentCode = xssfRow.getCell(5);
                    XSSFCell areaTypeCode = xssfRow.getCell(6);
                    XSSFCell areaNum = xssfRow.getCell(7);
                    XSSFCell zipCode = xssfRow.getCell(8);
                    XSSFCell remark = xssfRow.getCell(9);
                    
                    area.setAreaCode(getValue(areaCode));
                    area.setAreaName(getValue(areaName));
                    area.setAreaNameZw(getValue(areaNameZw));
                    area.setAreaLevelCode(getValue(areaLevleCode));
                    area.setAreaParentCode(getValue(areaParentCode));
                    area.setAreaTypeCode(getValue(areaTypeCode));
                    area.setAreaNum(getValue(areaNum));
                    area.setZipCode(getValue(zipCode));
                    area.setRemark(getValue(remark));
                    list.add(area);
                }
            }
        }
        return list;
    }

    /**
     * Read the Excel 2003-2007
     * @param path the path of the Excel
     * @return
     * @throws IOException
     */
    public List<AreaDTO> readXls(String path) throws IOException {
        System.out.println(PROCESSING + path);
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        AreaDTO area = null;
        List<AreaDTO> list = new ArrayList<AreaDTO>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= hssfSheet.getPhysicalNumberOfRows(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                	area = new AreaDTO();
                	
                    HSSFCell areaCode = hssfRow.getCell(0);
                    HSSFCell areaName = hssfRow.getCell(1);
                    HSSFCell areaNameZw = hssfRow.getCell(2);
                    HSSFCell areaLevleCode = hssfRow.getCell(3);
                    HSSFCell areaParentCode = hssfRow.getCell(4);
                    HSSFCell areaTypeCode = hssfRow.getCell(5);
                    HSSFCell areaNum = hssfRow.getCell(6);
                    HSSFCell zipCode = hssfRow.getCell(7);
                    HSSFCell remark = hssfRow.getCell(8);
                    
                    area.setAreaCode(getValue(areaCode));
                    area.setAreaName(getValue(areaName));
                    area.setAreaNameZw(getValue(areaNameZw));
                    area.setAreaLevelCode(getValue(areaLevleCode));
                    area.setAreaParentCode(getValue(areaParentCode));
                    area.setAreaTypeCode(getValue(areaTypeCode));
                    area.setAreaNum(getValue(areaNum));
                    area.setZipCode(getValue(zipCode));
                    area.setRemark(getValue(remark));
                    list.add(area);
                }
            }
        }
        return list;
    }

    
    
    @SuppressWarnings("static-access")
    private String getValue(XSSFCell xssfCell) {
        if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(xssfCell.getNumericCellValue());
        } else {
            return String.valueOf(xssfCell.getStringCellValue());
        }
    }

    @SuppressWarnings("static-access")
    private String getValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }
    
    public static void main(String[] args) {


        String ss="sssss";

        System.out.println(ss.getBytes());

        /*
    	Excel excel = new Excel();
    	String filePath = "/home/yuxiaoyue/git/guldan-thrall/src/main/webapp/WEB-INF/xlsx/area/20161120161616.xlsx";
    	try {
			List<AreaDTO> list = excel.readExcel(filePath);
			if (list != null)
				for (AreaDTO dto : list) {
					System.out.println(dto.getAreaName());
					//dto.setAreaNameZw();
				}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}*/
	}
    
    public String getPostfix(String path) {
        if (path == null || EMPTY.equals(path.trim())) {
            return EMPTY;
        }
        if (path.contains(POINT)) {
            return path.substring(path.lastIndexOf(POINT) + 1, path.length());
        }
        return EMPTY;
    }




}